package com.eascs.notice.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.file.FileTypeConstants;
import com.eascs.notice.dao.O2oNoticeLetterDao;
import com.eascs.notice.dao.OANoticeLetterDao;
import com.eascs.notice.entity.FormNoticeLetterEntity;
import com.eascs.notice.entity.O2oBankLoadDataEntity;
import com.eascs.notice.entity.O2oNoticeLetterFindEntity;
import com.eascs.notice.entity.OaBankLoadDataEntity;
import com.eascs.notice.entity.OaDataListFileEntity;
import com.eascs.notice.entity.OaNoticeLetterFindEntity;

@Service
public class O2oNoticeLetterService {

	@Autowired
	private O2oNoticeLetterDao noticeLetterDao;
	@Autowired
	private OANoticeLetterDao oaNoticeLetterDao;

	/**
	 * 查询新系统通知函基本信息
	 * 
	 * @return
	 */
	public PageResult<Object[]> getBasicDataLoan(FormNoticeLetterEntity entity) {
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT OL.BILL_CODE, CONCAT( OL.CUSTOMER_CODE, '-', OL.CUSTOME_RNAME ) AS CUSTOME_RNAME,CONCAT(OL.ltd_code,'-',OL.ltd_name) as LTD_NAME, OLU.CUSTOMER_NAME, ");
		querySql.append(" OL.REPAY_MENTWAY,FORMAT(OL.LOANRATE*100,1), OL.LOAN_TERM, FORMAT(OL.LOANA_MOUNT,2), ");
		querySql.append(" FORMAT(SUM(OBLD.LOAN_AMOUNT),2) AS LOAN_AMOUNT,OL.OPERATOR_NAME,OL.FILE_STATUS AS ISFILE ");
		querySql.append(" FROM O2O_LOANAPPLY OL RIGHT JOIN O2O_LOAN_USER OLU ON OL.LOAN_ID = OLU.LOAN_ID ");
		//querySql.append(" RIGHT JOIN O2O_PAYMENTS_PLAN OPP ON OL.BILL_CODE = OPP.BILL_CODE ");
		querySql.append(" JOIN O2O_BANK_LOAN_DATA OBLD ON OL.BILL_CODE = OBLD.BILL_CODE ");
		querySql.append(" WHERE /**DATA_PERMISSION(OL.LTD)**/ OL.LOANAPPLY_NO = OLU.LOANAPPLY_NO AND OL.BANK_LOAN_NAME IN ('BC','GACZ')");
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getShowPage1());
		if (!StringUtil.isEmpty(entity.getBill_code())) {
			querySql.append(" AND OL.BILL_CODE LIKE ? ");
			pageQuery.addLikeParam(entity.getBill_code());
		}
		if (!StringUtil.isEmpty(entity.getName())) {
			querySql.append(" AND OLU.CUSTOMER_NAME LIKE ? ");
			pageQuery.addLikeParam(entity.getName());
		}
		if (!StringUtil.isEmpty(entity.getIsUploadFile())) {
			querySql.append(" AND ol.file_status = ? ");
			pageQuery.addParam(entity.getIsUploadFile());
		}
		querySql.append(" GROUP BY OL.BILL_CODE ");
		pageQuery.setQuerySql(querySql.toString());
		return noticeLetterDao.findDataWithPage(pageQuery);
	}

	/**
	 * 查询新系统详情信息
	 * 
	 * @return
	 */
	public List<O2oNoticeLetterFindEntity> getNoticeLetterData(String billCode) {
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT OPP.PERIODS, OPP.PAYMENTS_DATE, OPP.PAY_PRINCIPAL, OPP.PAY_INTEREST, (OPP.PAY_PRINCIPAL+OPP.PAY_INTEREST) AS PAY_TOTAL");
		querySql.append(" FROM O2O_PAYMENTS_PLAN OPP WHERE OPP.BILL_CODE = ? and OPP.is_del = 'N'  ORDER BY OPP.PERIODS ASC ");
		return noticeLetterDao.findEntitys(querySql.toString(), O2oNoticeLetterFindEntity.class, billCode);
	}

	/**
	 * 查询新系统借款人姓名和放款时间
	 * 
	 * @param billCode
	 * @return
	 */
	public O2oBankLoadDataEntity getLoanUserNameAndDate(String billCode) {
		String sql = "SELECT OBLD.LOANER_NAME,OBLD.LOAN_DATE FROM O2O_BANK_LOAN_DATA OBLD WHERE OBLD.BILL_CODE=?";
		return noticeLetterDao.findEntity(sql, O2oBankLoadDataEntity.class, billCode);
	}

	/**
	 * 查询老系统通知函基本信息
	 * 
	 * @return
	 */
	/** DATA_PERMISSION(HU.UNITNO) **/
	public PageResult<Object[]> getOaBasicDataLoan(FormNoticeLetterEntity entity) {

		SqlPageQuery<Object[]> sqlPageQueryC = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sqlC = new StringBuffer("SELECT OA_BILL_CODE FROM OA_DATALIST WHERE 1=1");
		if (!StringUtil.isEmpty(entity.getBill_code())) {
			sqlC.append(" AND OA_BILL_CODE=? ");
			sqlPageQueryC.addParam(entity.getBill_code());
		}
		sqlPageQueryC.setQuerySql(sqlC.toString());
		PageResult<Object[]> pageOaC = noticeLetterDao.findDataWithPage(sqlPageQueryC);

		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT DISTINCT * FROM (SELECT OL.BILLCODE,OL.CUSTOMERCODE||'-'||OL.CUSTOMERNAME,");
		querySql.append(" HU.UNITCODE||'-'||HU.NAMECN AS NAMECN,ORF.LOANERNAME,");
		querySql.append("(SELECT PDD.ITEMNAME FROM EADB.PUB_DDITEM PDD JOIN EADB.PUB_DATADICTIONARY PD ON PDD.DDNO=PD.DDNO ");
		querySql.append(" WHERE PD.DDCODE='OTOREPAYMENTWAY' AND OL.REPAYMENTWAY = PDD.ITEMCODE) ITEMNAME, ");
		querySql.append(" OL.LOANRATE*100,OL.LOANTERM,TO_CHAR(OL.LOANAMOUNT,'999,999,999,999,999.00') ");
		querySql.append(" ,TO_CHAR(ORFU.BANKLOANAMOUNT,'999,999,999,999,999.00'),OL.OPERATORNAME ");
		querySql.append(" FROM EADB.O2O_LOANAPPLY OL JOIN EADB.O2O_REFUND ORF ON OL.BILLCODE=ORF.LOADAPPLYCODE");
		querySql.append(" JOIN EADB.HR_UNITS HU ON  HU.UNITNO = OL.LTD ");
		querySql.append(" JOIN EADB.O2O_REFUNDLINE ORFL ON ORFL.REFUNDNO = ORF.REFUNDNO ");
		querySql.append(" LEFT JOIN EADB.O2O_REFUND ORFU ON ORFU.LOADAPPLYCODE = OL.BILLCODE ");
		querySql.append(" WHERE /**DATA_PERMISSION(HU.UNITNO)**/ ORFU.ISLOCK='N' AND ORFU.BANKREFUNDCODE IN ('GACZ','BC') ) T WHERE 1=1 ");
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getShowPage());
		if (!StringUtil.isEmpty(entity.getIsUploadFile()) && null != pageOaC && pageOaC.getData().size() > 0) {
			StringBuffer dataP = new StringBuffer();
			for (int i = 0; i < pageOaC.getData().size(); i++) {
				dataP.append("'" + pageOaC.getData().get(i)[0] + "',");
			}
			String d = "";
			if(dataP.length()>0){
			    d = dataP.substring(0, dataP.length() - 1);
			}
			if ("N".equals(entity.getIsUploadFile())) {
				querySql.append(" AND T.BILLCODE NOT IN ( ");
				querySql.append(d);
				querySql.append(" )");
			} else if ("Y".equals(entity.getIsUploadFile())) {
				querySql.append(" AND T.BILLCODE IN ( ");
				querySql.append(d);
				querySql.append(" )");
			}
		} else {
			if ("Y".equals(entity.getIsUploadFile())) {
				querySql.append(" AND T.BILLCODE IN ('')");
			}
		}
		
		if (!StringUtil.isEmpty(entity.getBill_code())) {
			querySql.append(" AND T.BILLCODE LIKE ? ");
			pageQuery.addLikeParam(entity.getBill_code());
		}
		if (!StringUtil.isEmpty(entity.getName())) {
			querySql.append(" AND T.LOANERNAME LIKE ? ");
			pageQuery.addLikeParam(entity.getName());
		}
		querySql.append(" ORDER BY T.BILLCODE DESC ");
		pageQuery.setQuerySql(querySql.toString());
		PageResult<Object[]> oa = oaNoticeLetterDao.findDataWithPage(pageQuery);
		PageResult<Object[]> oaIsFile = new PageResult<>();
		List<Object[]> oaAddO2o = new ArrayList<>();
		Object[] oaO2o = null;
		for (int i = 0; i < oa.getData().size(); i++) {
			int count = 1;
			oaO2o = new Object[oa.getData().get(i).length];
			String billCode = oa.getData().get(i)[0].toString();
			String string = "";
			String sql1 = "select id from oa_datalist where oa_bill_code=?";
			List<String> stringD = noticeLetterDao.findSimpleObjectList(sql1, String.class, billCode);
			if (stringD != null && stringD.size() > 0) {
				string = stringD.get(0);
			}
			String isFile = StringUtil.isEmpty(string) ? "未上传" : "已上传";
			for (int j = 0; j < oa.getData().get(i).length - 1; j++) {
				count++;
				oaO2o[j] = oa.getData().get(i)[j];
				if (count == oa.getData().get(i).length) {
					oaO2o[oa.getData().get(i).length - 1] = isFile;
				}
			}
			oaAddO2o.add(oaO2o);
		}
		oaIsFile.setData(oaAddO2o);
		oaIsFile.setData(oaAddO2o);
		oaIsFile.setDbRowCount(oa.getDbRowCount());
		oaIsFile.setPageIndex(oa.getPageIndex());
		oaIsFile.setPageSize(oa.getPageSize());
		oaIsFile.setPageTotal(oa.getPageTotal());
		oaIsFile.setReturnDataSize(oa.getReturnDataSize());
		return oaIsFile;
	}

	/**
	 * 查询老系统详情信息
	 * 
	 * @return
	 */
	public List<OaNoticeLetterFindEntity> getOaNoticeLetterData(String billCode) {
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT OTOR.TERMNO,OTOR.INTERESTDATE,OTOR.ARPRINCIPAL,OTOR.ARINTEREST,OTOR.ARPRINCIPAL+OTOR.ARINTEREST AS HRPRINCIPAL ");
		querySql.append(" FROM EADB.O2O_REFUNDLINE OTOR JOIN EADB.O2O_REFUND OTR ON OTOR.REFUNDNO = OTR.REFUNDNO ");
		querySql.append(" WHERE OTR.LOADAPPLYCODE = ? ");
		return oaNoticeLetterDao.findEntitys(querySql.toString(), OaNoticeLetterFindEntity.class, billCode);
	}

	/**
	 * 查询新系统借款人姓名和放款时间
	 * 
	 * @param billCode
	 * @return
	 */
	public OaBankLoadDataEntity getOaLoanUserNameAndDate(String billCode) {
		String sql = "SELECT ORO.LOANERNAME,ORO.LOADDATE FROM EADB.O2O_REFUND ORO WHERE ORO.LOADAPPLYCODE = ?";
		return oaNoticeLetterDao.findEntity(sql, OaBankLoadDataEntity.class, billCode);
	}
	
	/**
	 * @Description: <p> 查询老系统放款银行  </p>
	 * @author CXW
	 * @date 2016年9月13日 下午5:22:01
	 * @version V1.0
	 * @param 
	 * @return String
	 */
	public String getBankLoanName(String billCode){
		String sql = "SELECT ORR.BANKTYPE FROM EADB.O2O_REFUND ORR WHERE ORR.LOADAPPLYCODE=?";
		return oaNoticeLetterDao.findString(sql, billCode);
	}

	/**
	 * 老系统上传通知函附件
	 * 
	 * @param file
	 * @param datalist
	 * @param billCode
	 */
	public void oaUpload(MultipartFile file, String datalist, String billCode) {
		noticeLetterDao.oaUploadFile(file, datalist, billCode);
	}

	/**
	 * 新系统上传通知函附件
	 * 
	 * @param file
	 * @param datalist
	 * @param billCode
	 */
	public void o2oUpload(MultipartFile file, String datalist, String billCode) {
		String sql = "UPDATE O2O_LOANAPPLY SET FILE_STATUS='Y' WHERE BILL_CODE = ?";
		noticeLetterDao.execute(sql, billCode);
		noticeLetterDao.o2oUploadFile(file, datalist, billCode);
	}

	/**
	 * 查询老系统通知函附件路径
	 * 
	 * @param datalist
	 * @param billCode
	 * @return
	 */
	public String oaFindUploadFilePath(String billCode) {
		String sql = "select id from oa_datalist where oa_bill_code=?";
		String string = "";// noticeLetterDao.findString(sql, billCode);
		List<String> stringD = noticeLetterDao.findSimpleObjectList(sql, String.class, billCode);
		if (stringD != null && stringD.size() > 0) {
			string = stringD.get(0);
		}
		return string;
	}

	/**
	 * 查询新系统通知函附件路径
	 * 
	 * @param datalist
	 * @param billCode
	 * @return
	 */
	public String o2oFindUploadFilePath(String billCode) {
		String sql = "select datalist_no from o2o_datalist where loanapply_no=? and datalist_code=?";
		String string = "";// noticeLetterDao.findString(sql, billCode,
							// FileTypeConstants.DALIST_TYPE_NOTICE)
		List<String> stringD = noticeLetterDao.findSimpleObjectList(sql, String.class, billCode, FileTypeConstants.DALIST_TYPE_NOTICE);
		if (stringD != null && stringD.size() > 0) {
			string = stringD.get(0);
		}
		return string;
	}

	/**
	 * 查询OA上传的文件对象
	 * 
	 * @param id
	 * @return
	 */
	public OaDataListFileEntity findOaDataListFileEntity(String id) {
		return noticeLetterDao.getById(OaDataListFileEntity.class, id);
	}
}
